﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;


/// <summary>
/// Summary description for AdminManagement
/// </summary>
public class AdminManagement
{
    #region const declaration
    public const string TABLE_NAME = "Admin";
    public const string ADMINID = "adminID";
    public const string ADMINNAME = "adminName";
    public const string USERNAME = "username";
    public const string PASS = "pass";
    public const string EMAIL = "email";
    public const string TEL = "tel";
    #endregion

    public AdminManagement()
	{
	}

    public static bool AddAdmin(string UserName, string Pass, string FullName, string Email,  string Phone)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "INSERT INTO Admin Values(@FullName,@UserName,@Pass,@Email,@Phone);SELECT scope_identity()";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@FullName", FullName);
        command.Parameters.AddWithValue("@UserName", UserName);
        command.Parameters.AddWithValue("@Pass", Pass);
        command.Parameters.AddWithValue("@Email", Email);
        command.Parameters.AddWithValue("@Phone", Phone);
        
        int n = int.Parse(command.ExecuteScalar().ToString());

        cnn.Close();

        if (n > 0)
            return true;
        else
            return false;
    }

    public static bool CheckUser(string UserName, string Pass)
    {
        //Open connect to database:
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        //Check UserName and Pass:
        string sqlQuery = "SELECT COUNT(*) From Admin WHERE username=@UserName and pass=@Pass";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@UserName", UserName);
        command.Parameters.AddWithValue("@Pass", Pass);
        int n = int.Parse(command.ExecuteScalar().ToString());
        if (n == 0)
            return false;
        else
            return true;
    }

    public static bool CheckUserName(string UserName)
    {
        //Open connect to database:
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        //Check UserName:
        string sqlQuery = "SELECT COUNT(*) From Admin WHERE username=@UserName";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@UserName", UserName);
        int n = int.Parse(command.ExecuteScalar().ToString());
        if (n == 0)
            return false;
        else
            return true;
    }

    public static bool ResetPass(string UserName)
    {
        //Open connect to database:
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Admin SET pass=@Pass WHERE  username=@UserName";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@Pass", "123456");
        command.Parameters.AddWithValue("@UserName", UserName);
        int n = command.ExecuteNonQuery();
        if (n == 0)
            return false;
        else
            return true;
    }

    public static bool ChangePass(string UserName, string NewPass)
    {
        //Open connect to database:
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Admin SET pass=@NewPass WHERE  username=@UserName";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@NewPass", NewPass);
        command.Parameters.AddWithValue("@UserName", UserName);
        int n = command.ExecuteNonQuery();
        if (n == 0)
            return false;
        else
            return true;
    }

    public static bool EditAdmin(string FullName, string Email, string Phone, string UserName)
    {
        //Open connect to database:
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Admin Set adminName=@FullName,email=@email,tel=@Phone WHERE username=@UserName";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@FullName", FullName);
        command.Parameters.AddWithValue("@email", Email);
        command.Parameters.AddWithValue("@Phone", Phone);
        command.Parameters.AddWithValue("@UserName", UserName);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }

    public static bool UpdateAdmin(string UserName, string password, string FullName, string Email, string Phone,  int adminID)
    {
        //Open connect to database:
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Admin Set username=@username,pass=@password,adminName=@FullName,email=@email,tel=@Phone WHERE adminID=@adminID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@username", UserName);
        command.Parameters.AddWithValue("@password", password);
        command.Parameters.AddWithValue("@FullName", FullName);
        command.Parameters.AddWithValue("@email", Email);
        command.Parameters.AddWithValue("@Phone", Phone);
        command.Parameters.AddWithValue("@adminID", adminID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }

    public static bool DeleteAdmin(int adminID)
    {
        //Open connect to database:
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE Admin WHERE adminID=@adminID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@adminID", adminID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }

    public static DataSet GetAllAdmin()
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        string sqlQuery = "SELECT * FROM Admin ORDER BY adminName";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllAdmin = new DataSet();
        adapter.Fill(dsAllAdmin, "AllAdmin");

        return dsAllAdmin;
    }

    public static DataSet GetAllAdminNotUser(string username)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        //string sqlQuery = "SELECT * FROM Admin WHERE username not like '" + username + "' ORDER BY adminName";
        //SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY T.tourID ASC) AS ROW_NUM, T.* FROM TOURS T WHERE T.tourID = 10) AS SUB_SQL
        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY A.adminName ASC) AS NO, A.* FROM Admin A WHERE username not like '" + username + "') AS SUB_SQL";
        
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, strCon);

        DataSet dsAdmins = new DataSet();
        adapter.Fill(dsAdmins, "Admins");

        return dsAdmins;
    }

    public static DataSet GetUser(string username, string password)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        string sqlQuery = "SELECT * FROM Admin WHERE username ='"+username+"' AND pass='"+password+"'";

        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, strCon);

        DataSet dsAdmin = new DataSet();
        adapter.Fill(dsAdmin, "Admin");

        return dsAdmin;
    }

    public static DataSet GetAdminByUser(string username)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        string sqlQuery = "SELECT * FROM Admin WHERE username='" + username + "'";

        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, strCon);
        DataSet dsAdmin = new DataSet();
        adapter.Fill(dsAdmin, "Admin");
        return dsAdmin;
    }
}